package model;

import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import dbcTools.DB_Operation;

public class UserDBTO {
	
	private final static String tableName = "user";
	
	private DB_Operation dbOperation;
	
	public UserDBTO(Connection connection) {
		this.dbOperation = new DB_Operation(connection, UserDBTO.tableName);
	}
	
	public void setConnection(Connection connection) {
		this.dbOperation.setConnection(connection);
	}
	public void setAutoCommit(Boolean autoCommit) throws SQLException {
		this.dbOperation.setAutoCommit(autoCommit);
	}
	public void commit() throws SQLException {
		this.dbOperation.commit();
	}
	

	/**
	 * insert
	 */
	public int addNewUser(String avatar_type, Blob avatar, String name, String address, 
			String account, String password) throws SQLException, IOException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.insert("avatar_type", avatar_type)
				.insert("avatar", avatar).insert("name", name).insert("address", address)
				.insert("account", account).insert("password", password)
				.insert("wechat_number", account)
				.insert("creation_time", new Timestamp(System.currentTimeMillis()))
				.executeUpdate();
	}

	/**
	 * delete
	 */
	public int deleteUserById(Connection connection, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.delete()
				.where("id", "=", id)
				.executeUpdate();
	}
	public int deleteUserByAccount(Connection connection, String account) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.delete()
				.where("account", "=", account)
				.executeUpdate();
	}
	/**
	 * update
	 */
	public int updateAvatar_typeById(Connection connection, String avatar_type, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("avatar_type", avatar_type)
				.where("id", "=", id)
				.executeUpdate();
	}
	public int updateAvatarById(Connection connection, Blob avatar, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("avatar", avatar)
				.where("id", "=", id)
				.executeUpdate();
	}
	public int updateNameById(Connection connection, String name, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("name", name)
				.where("id", "=", id)
				.executeUpdate();
	}
	public  int updateGenderById(Connection connection, String gender, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("gender", gender)
				.where("id", "=", id)
				.executeUpdate();
	}
	public int updateAddressById(Connection connection, String address, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("address", address)
				.where("id", "=", id)
				.executeUpdate();
	}
	public int updateSignById(Connection connection, String sign, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("sign", sign)
				.where("id", "=", id)
				.executeUpdate();
	}
	public int updateAccountById(Connection connection, String account, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("account", account)
				.where("id", "=", id)
				.executeUpdate();
	}
	
	public int updatePasswordById(Connection connection, String password, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("password", password)
				.where("id", "=", id)
				.executeUpdate();
	}
	public int updateWechat_numberById(Connection connection, String wechat_number, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("wechat_number", wechat_number)
				.where("id", "=", id)
				.executeUpdate();
	}
	public int updateWechat_number_setedById(Connection connection, Boolean wechat_number_seted, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		return this.dbOperation
				.set("wechat_number_seted", wechat_number_seted)
				.where("id", "=", id)
				.executeUpdate();
	}
	/**
	 * select
	 */
	public User getUserById(Connection connection, int id) throws SQLException {
		dbOperation.prepareNextOperation();
		User user = new User();

		ResultSet resultSet = this.dbOperation.select("*").where("id", "=", id).executeQuery();

		if (resultSet.next()) {
			user.setId(resultSet.getInt("id"))
				.setAvatar_type(resultSet.getString("avatar_type"))
				.setAvatar(resultSet.getBlob("avatar"))
				.setName(resultSet.getString("name"))
				.setGender(resultSet.getString("gender"))
				.setAddress(resultSet.getString("address"))
				.setSign(resultSet.getString("sign"))
				.setAccount(resultSet.getString("account"))
				.setPassword(resultSet.getString("password"))
				.setWechat_number(resultSet.getString("wechat_number"))
				.setWechat_number_seted(resultSet.getBoolean("wechat_number_seted"))
				.setCreation_time(resultSet.getTimestamp("creation_time"));
			return user;
		} else {
			return null;
		}
	}
	
	public User getUserByAccount(Connection connection, String account) throws SQLException {
		dbOperation.prepareNextOperation();
		User user = new User();

		ResultSet resultSet = this.dbOperation.select("*").where("account", "=", account).executeQuery();

		if (resultSet.next()) {
			user.setId(resultSet.getInt("id"))
				.setAvatar_type(resultSet.getString("avatar_type"))
				.setAvatar(resultSet.getBlob("avatar"))
				.setName(resultSet.getString("name"))
				.setGender(resultSet.getString("gender"))
				.setAddress(resultSet.getString("address"))
				.setSign(resultSet.getString("sign"))
				.setAccount(resultSet.getString("account"))
				.setPassword(resultSet.getString("password"))
				.setWechat_number(resultSet.getString("wechat_number"))
				.setWechat_number_seted(resultSet.getBoolean("wechat_number_seted"))
				.setCreation_time(resultSet.getTimestamp("creation_time"));
			return user;
		} else {
			return null;
		}
	}
	
	/**
	 * exist
	 */
	public boolean existByAccount(Connection connection, String account) throws SQLException {
		dbOperation.prepareNextOperation();
		DB_Operation dbo = this.dbOperation;
		
		ResultSet resultSet = dbo.selectOnly(
				dbo.prepareSubOperation().exists(
						dbo.prepareSubOperation().select("id").where("account", "=", account).getSql()
				).getSql()
			).executeQuery();
		if (resultSet.next()) {
			return resultSet.getBoolean(1);
		} else {
			return false;
		}
	}
}
